package dao.mysql;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import model.LeaveDTO;
import model.PositionDTO;
import model.UserDTO;
import exception.DAOSystemException;
import dao.LeaveDAO;

public class LeaveDAOImpl implements LeaveDAO {
	MySqlImpl mysqlImpl = new MySqlImpl();

	@Override
	public LeaveDTO findLeave(String id) throws DAOSystemException {
		// TODO Auto-generated method stub
		String selectSql = "SELECT * FROM `leave` WHERE id='" + id.trim() + "';";
		LeaveDTO oneLeave = new LeaveDTO();
		try {
			ResultSet rs = mysqlImpl.get(selectSql);
			String userId = null;
			String leaveTypeId = null;
			while (rs.next()) {
				oneLeave.setId(rs.getString("id"));
				oneLeave.setFrom(rs.getDate("from"));
				oneLeave.setTo(rs.getDate("to"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setLeaveNature(rs.getString("leaveNature"));
				oneLeave.setWorkDissemination(rs.getString("workDissemination"));
				oneLeave.setContactDetails(rs.getString("contactDetails"));
				leaveTypeId = rs.getString("leaveTypeId");
				oneLeave.setLeaveType(new LeaveTypeDAOImpl()
						.findLeaveType(leaveTypeId));
				
				userId = rs.getString("userId");
				
				System.out.println("USERID"+ userId);
				System.out.println("..............."+new UserDAOImpl().findUser(userId));
				oneLeave.setUser(new UserDAOImpl().findUser(userId));
				oneLeave.setApplyDate(rs.getDate("applyDate"));

			}

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

		return oneLeave;

	}

	@Override
	public ArrayList<LeaveDTO> findAllLeaves() throws DAOSystemException {
		// TODO Auto-generated method stub

		ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();
		String selectSql = "SELECT * FROM `leave`;";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			LeaveDTO oneLeave = null;
			String leaveTypeId = null;
			String userId = null;
			while (rs.next()) {
				oneLeave = new LeaveDTO();
				oneLeave.setId(rs.getString("id"));
				oneLeave.setFrom(rs.getDate("from"));
				oneLeave.setTo(rs.getDate("to"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setLeaveNature(rs.getString("leaveNature"));
				oneLeave.setWorkDissemination(rs.getString("workDissemination"));
				oneLeave.setContactDetails(rs.getString("contactDetails"));
				leaveTypeId = rs.getString("leaveTypeId");
				oneLeave.setLeaveType(new LeaveTypeDAOImpl()
						.findLeaveType(leaveTypeId));
				userId = rs.getString("userId");
				oneLeave.setUser(new UserDAOImpl().findUser(userId));
				oneLeave.setApplyDate(rs.getDate("applyDate"));
				items.add(oneLeave);
			}

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	@Override
	public void insertLeave(LeaveDTO leave) throws DAOSystemException {
		// TODO Auto-generated method stub
		String insertSql = "INSERT INTO leaveapplicationdb.leave VALUES('" + leave.getId()
				+ "', '" + leave.getFrom() + "', '" + leave.getTo() + "', '"
				+ leave.getStatus() + "', '" + leave.getReason() + "', '"
				+ leave.getLeaveNature() + "', '"
				+ leave.getWorkDissemination() + "', '"
				+ leave.getContactDetails() + "', '"
				+ leave.getLeaveType().getId() + "', '"
				+ leave.getUser().getId() + "', '" + leave.getApplyDate()
				+ "');";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			int res = mysqlImpl.execute(insertSql);

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}


/*	public void updateLeave(LeaveDTO leave) throws DAOSystemException {
		// TODO Auto-generated method stub
		String updateSql = "UPDATE leave SET from = '" + leave.getFrom()
				+ "', to = '" + leave.getTo() + "', status = '"
				+ leave.getStatus() + "', reason = '" + leave.getReason()
				+ "', leaveNature = '" + leave.getLeaveNature()
				+ "', workDissemination = '" + leave.getWorkDissemination()
				+ "', contactDetails = '" + leave.getContactDetails()
				+ "', leaveTypeId = '" + leave.getLeaveType().getId()
				+ "', userId = '" + leave.getUser().getId()
				+ "', applyDate = '" + leave.getApplyDate() + "' WHERE id='"
				+ leave.getId() + "';";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = mysqlImpl.execute(updateSql);

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}*/
	
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveapplicationdb";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	
	public void updateLeave(LeaveDTO leave) throws DAOSystemException {
//		String updateSql = "UPDATE leave SET from = '" + leave.getFrom() + "';";
		String i = "Apply";
	//	String updateSql = "UPDATE leaveapplicationdb.leave SET leaveapplicationdb.leave.from = '" + leave.getFrom() + "' WHERE id='"
	//	+ leave.getId() + "';";
		
		leave.setStatus(i);
		String updateSql = "UPDATE leaveapplicationdb.leave SET leaveapplicationdb.leave.from = '" + leave.getFrom()
		+ "', leaveapplicationdb.leave.to = '" + leave.getTo()
		+ "', status = '"
		+ leave.getStatus()
		+ "', reason = '"
		+ leave.getReason()
		+ "', leaveNature = '"
		+ leave.getContactDetails()
		+ "', leaveTypeId = '"
		+ leave.getLeaveType().getId()
		+ "' WHERE id='"
		+ leave.getId() + "';";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = (Connection) DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = (Statement) conn.createStatement();
//			Logger.getLogger(getClass().getName()).log(Level.INFO,
//					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	@Override
	public void deleteLeave(LeaveDTO leave) throws DAOSystemException {
		// TODO Auto-generated method stub
		String deleteSql = "DELETE FROM leaveapplicationdb.leave WHERE id='"
				+ leave.getId().trim() + "';";

		try {
			mysqlImpl.execute(deleteSql);
		} catch (SQLException e) {
			String error = "Error deleting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			try {
				mysqlImpl.rollBack();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}

	//@Thiri start
	@Override
	public ArrayList<LeaveDTO> findAllLeaveByManagerID(String userid)
			throws DAOSystemException {
		// String selectSql = "SELECT  id from user where mgrId='" +
		// userid.trim()+ "';";
		// manager.getId(

		String selectSql = "select l.id,l.from ,l.to ,l.status,l.reason,l.contactDetails,l.leaveTypeId,l.applyDate,lt.type  FROM leaveApplicationdb.user uu,leaveApplicationdb.leave l,leaveApplicationdb.leavetype lt WHERE   uu.id=l.userId AND l.leaveTypeId=lt.id AND l.status='Apply' AND uu.mgrId='"+ userid.trim() + "';";

		ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			LeaveDTO oneLeave = null;
			String leaveTypeId = null;
			while (rs.next()) {
				oneLeave = new LeaveDTO();
				oneLeave.setId(rs.getString("id"));
				oneLeave.setFrom(rs.getDate("from"));
				// System.out.println(rs.getDate("from"));
				oneLeave.setTo(rs.getDate("to"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setContactDetails(rs.getString("contactDetails"));
				// leaveTypeId = rs.getString("leaveTypeId");
				// oneLeave.setLeaveType(new
				// LeaveTypeDAOImpl().findLeaveType(leaveTypeId));
				oneLeave.setApplyDate(rs.getDate("applyDate"));
				oneLeave.setLeaveType(new LeaveTypeDAOImpl().findLeaveType(rs
						.getString("leaveTypeId")));
				oneLeave.getLeaveType().getType();
				items.add(oneLeave);
			}

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public LeaveDTO findLeaveDetailByID(String leaveId)
			throws DAOSystemException {

		String selectSql = "select l.id,uu.name,l.from ,l.to,l.applyDate,l.status,l.reason,l.leaveNature,l.workDissemination,l.contactDetails,l.leaveTypeId,lt.type FROM leaveApplicationdb.user uu,leaveApplicationdb.leave l,leaveApplicationdb.leavetype lt  WHERE   uu.id=l.userId AND l.leaveTypeId=lt.id AND  l.userId=uu.id AND  l.id='"
				+ leaveId.trim() + "';";

		/* ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>(); */
		LeaveDTO oneLeave = new LeaveDTO();

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			// LeaveDTO oneLeave = null;
			String leaveTypeId = null;
			if (rs.next()) {
				oneLeave = new LeaveDTO();
				oneLeave.setId(rs.getString("id"));
				oneLeave.setStaffName(rs.getString("name"));
				System.out.println(rs.getString("name"));
				// oneLeave.setId(rs.getString("id"));
				oneLeave.setFrom(rs.getDate("from"));
				oneLeave.setTo(rs.getDate("to"));
				oneLeave.setApplyDate(rs.getDate("applyDate"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setLeaveNature(rs.getString("leaveNature"));
				oneLeave.setWorkDissemination(rs.getString("workDissemination"));
				oneLeave.setContactDetails(rs.getString("contactDetails"));
				// leaveTypeId = rs.getString("leaveTypeId");
				oneLeave.setLeaveType(new LeaveTypeDAOImpl().findLeaveType(rs
						.getString("leaveTypeId")));
				oneLeave.getLeaveType().getType();

			}

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return oneLeave;

	}

	public ArrayList<LeaveDTO> findAllSubordinateByManagerId(String mgrId) {

		String selectSql = "select uu.name,l.id,l.from ,l.to ,l.status,l.reason,l.contactDetails,l.leaveTypeId,l.applyDate,lt.type FROM leaveApplicationdb.user uu,leaveApplicationdb.leave l,leaveApplicationdb.leavetype lt  WHERE   uu.id=l.userId AND l.leaveTypeId=lt.id AND  uu.mgrId='"
				+ mgrId.trim() + "' order by uu.userName ;";

		ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			LeaveDTO oneLeave = null;
			String leaveTypeId = null;
			while (rs.next()) {
				oneLeave = new LeaveDTO();
				oneLeave.setStaffName(rs.getString("name"));
				System.out.println(rs.getString("name"));
				oneLeave.setId(rs.getString("id"));
				oneLeave.setFrom(rs.getDate("from"));

				oneLeave.setTo(rs.getDate("to"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setContactDetails(rs.getString("contactDetails"));
				oneLeave.setApplyDate(rs.getDate("applyDate"));
				oneLeave.setLeaveType(new LeaveTypeDAOImpl().findLeaveType(rs
						.getString("leaveTypeId")));
				oneLeave.getLeaveType().getType();
				items.add(oneLeave);
			}

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			try {
				throw new DAOSystemException(error);
			} catch (DAOSystemException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}

		}
		return items;
	}

	public void UpdateLeave(String id, String status, String reason) {

		String updateSql = "UPDATE  leaveApplicationdb.leave SET status = '"
				+ status + "', reason = '" + reason + "' WHERE id='" + id
				+ "';";

		System.out.println("Update........");

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + updateSql);
			int rs = mysqlImpl.execute(updateSql);

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			try {
				throw new DAOSystemException(error);
			} catch (DAOSystemException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}

		}

	}

	
	
	public ArrayList<LeaveDTO> findAllLeaveByUserId(String userid) throws DAOSystemException
	{
		String selectSql = "select l.id,l.from ,l.to ,l.status,l.reason,l.contactDetails,l.leaveTypeId,l.applyDate,lt.type  FROM leaveApplicationdb.user uu,leaveApplicationdb.leave l,leaveApplicationdb.leavetype lt  WHERE   uu.id=l.userId  AND l.leaveTypeId=lt.id  AND  uu.id='" + userid.trim() + "';";
		
	ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();

	try {

		Logger.getLogger(getClass().getName()).log(Level.INFO,
				"Executing select: " + selectSql);
		ResultSet rs = mysqlImpl.get(selectSql);
		
		LeaveDTO oneLeave = null;
		String leaveTypeId = null;
		while (rs.next()) {
			oneLeave = new LeaveDTO();
			oneLeave.setId(rs.getString("id"));
			oneLeave.setFrom(rs.getDate("from"));
			oneLeave.setTo(rs.getDate("to"));
			oneLeave.setStatus(rs.getString("status"));
			oneLeave.setReason(rs.getString("reason"));
			oneLeave.setContactDetails(rs.getString("contactDetails"));
			oneLeave.setApplyDate(rs.getDate("applyDate"));
			oneLeave.setLeaveType(new LeaveTypeDAOImpl().findLeaveType(rs.getString("leaveTypeId")));
			oneLeave.getLeaveType().getType();
			System.out.println(oneLeave.toString());
			items.add(oneLeave);
		}

	} catch (Exception e) {
		String error = "Error selecting leave. Nested Exception is: " + e;
		Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
		throw new DAOSystemException(error);
	} finally {
		try {
			mysqlImpl.close();
		} catch (Exception e) {
		}
	}
	return items;

	}//Thiri end

	/* Start: Added By : Suganthi*/
	public ArrayList<LeaveDTO> findAllLeavesByUser(String userid) throws DAOSystemException {
		// TODO Auto-generated method stub

		ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();
		String selectSql = "SELECT * FROM leaveApplicationdb.leave WHERE userId='" + userid.trim() +  "'" +
		"and year(" + "applyDate)" +"=year("+"curdate("+ ")"+ ");";
		
		;
		//System.out.println(selectSql);
		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			LeaveDTO oneLeave = null;
			String leaveTypeId = null;
			while (rs.next()) {
				oneLeave = new LeaveDTO();
				oneLeave.setId(rs.getString("id"));
				oneLeave.setFrom(rs.getDate("from"));
				oneLeave.setTo(rs.getDate("to"));
				oneLeave.setStatus(rs.getString("status"));
				oneLeave.setReason(rs.getString("reason"));
				oneLeave.setLeaveNature(rs.getString("leaveNature"));
				oneLeave.setWorkDissemination(rs.getString("workDissemination"));
				oneLeave.setContactDetails(rs.getString("contactDetails"));
				leaveTypeId = rs.getString("leaveTypeId");
				oneLeave.setLeaveType(new LeaveTypeDAOImpl().findLeaveType(leaveTypeId));
		        oneLeave.setApplyDate(rs.getDate("applyDate"));
				items.add(oneLeave);
			}

		} catch (Exception e) {
			String error = "Error selecting leave. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}
	/* End: Added By : Suganthi */
}
